<?php
Class order_model extends CI_Model
{
	function __construct()
	{
		parent::__construct();
	}
	
	function get_gross_monthly_sales($year)
	{
		$this->db->select('SUM(coupon_discount) as coupon_discounts');
		$this->db->select('SUM(gift_card_discount) as gift_card_discounts');
		$this->db->select('SUM(subtotal) as product_totals');
		$this->db->select('SUM(shipping) as shipping');
		$this->db->select('SUM(tax) as tax');
		$this->db->select('SUM(total) as total');
		$this->db->select('YEAR(ordered_on) as year');
		$this->db->select('MONTH(ordered_on) as month');
		$this->db->group_by(array('MONTH(ordered_on)'));
		$this->db->order_by("ordered_on", "desc");
		$this->db->where('YEAR(ordered_on)', $year);
		$this->db->get('orders')->result();
		echo $this->db->last_query();
	}
	function get_order_year_sales($year){
		return $this->db->select("orders.*,order_history.content,MONTH(orders.ordered_on) as month")->where("YEAR(orders.ordered_on)",$year)->order_by("MONTH(orders.ordered_on)")->join("order_history","order_history.order_number = orders.id","left")->where("status","Delivered")->get("orders")->result();	
	}
	function get_sales_years(){
		$this->db->order_by("ordered_on", "desc");
		$this->db->select('YEAR(ordered_on) as year');
		$this->db->group_by('YEAR(ordered_on)');
		$records	= $this->db->get('orders')->result();
		$years		= array();
		foreach($records as $r)
		{
			$years[]	= $r->year;
		}
		return $years;
	}
	
	function get_orders($search=false, $sort_by='', $sort_order='DESC', $limit=0, $offset=0)
	{			
		if ($search)
		{
			if(!empty($search->term))
			{
				//support multiple words
				$term = explode(' ', $search->term);

				foreach($term as $t)
				{
					$not		= '';
					$operator	= 'OR';
					if(substr($t,0,1) == '-')
					{
						$not		= 'NOT ';
						$operator	= 'AND';
						//trim the - sign off
						$t		= substr($t,1,strlen($t));
					}

					$like	= '';
					$like	.= "( orders.order_number ".$not."LIKE '%".$t."%' " ;
					$like	.= $operator." `bill_firstname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `bill_lastname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `ship_firstname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `ship_lastname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `status` ".$not."LIKE '%".$t."%' ";
					$like	.= $operator." `notes` ".$not."LIKE '%".$t."%' ";
					$like	.= $operator." `fullname` ".$not."LIKE '%".$t."%' ";
					$like	.= $operator." `phone` ".$not."LIKE '%".$t."%' )";
					
					$this->db->where($like);
				}
					
			}
			if(!empty($search->start_date))
			{
				$start_date = date('Y-m-d',strtotime(str_replace("-","/",$search->start_date)));
				$this->db->where('ordered_on >=',$start_date);
			}
			if(!empty($search->end_date))
			{
				//increase by 1 day to make this include the final day
				//I tried <= but it did not function. Any ideas why?
				//$search->end_date = date('Y-m-d',strtotime(str_replace("-","/",$search->end_date))+86400);
				$end_date = date('Y-m-d',strtotime(str_replace("-","/",$search->end_date))+86400);
				$this->db->where('ordered_on <',$end_date);
			}
			
			if(!empty($search->status)){
			
				$this->db->where('status',$search->status);	
			}
			
		}
		
		if($limit>0)
		{
			$this->db->limit($limit, $offset);
		}
		if(!empty($sort_by))
		{
			$this->db->order_by($sort_by, $sort_order);
		}
		$this->db->join("order_history","order_history.order_number = orders.id","left");
		$this->db->select("orders.*,order_history.content as order_history");
		return $this->db->get('orders')->result();
	}
	function get_orders_count($search=false)
	{			
		if ($search)
		{
			if(!empty($search->term))
			{
				//support multiple words
				$term = explode(' ', $search->term);

				foreach($term as $t)
				{
					$not		= '';
					$operator	= 'OR';
					if(substr($t,0,1) == '-')
					{
						$not		= 'NOT ';
						$operator	= 'AND';
						//trim the - sign off
						$t		= substr($t,1,strlen($t));
					}

					$like	= '';
					$like	.= "( `order_number` ".$not."LIKE '%".$t."%' " ;
					$like	.= $operator." `bill_firstname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `bill_lastname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `ship_firstname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `ship_lastname` ".$not."LIKE '%".$t."%'  ";
					$like	.= $operator." `status` ".$not."LIKE '%".$t."%' ";
					$like	.= $operator." `notes` ".$not."LIKE '%".$t."%' )";

					$this->db->where($like);
				}	
			}
			if(!empty($search->start_date))
			{
				$this->db->where('ordered_on >=',$search->start_date);
			}
			if(!empty($search->end_date))
			{
				$this->db->where('ordered_on <',$search->end_date);
			}
			
		}
		
		return $this->db->count_all_results('orders');
	}

	
	
	//get an individual customers orders
	function get_customer_orders($id, $offset=0){
		$this->db->join('order_items', 'orders.id = order_items.order_id');
		$this->db->order_by('ordered_on', 'DESC');
		$this->db->group_by('orders.id');
		return $this->db->get_where('orders', array('customer_id'=>$id), 15, $offset)->result();
	}
	
	function count_customer_orders($id)
	{
		$this->db->where(array('customer_id'=>$id));
		return $this->db->count_all_results('orders');
	}
	
	function get_order($id,$type=0){
		$this->db->where('orders.id', $id);
		if($type == 1){
			$this->db->select("orders.*,order_history.content as history");
			$this->db->join("order_history","order_history.order_number = orders.id","left");	
		}
		$result 			= $this->db->get('orders');
		$order				= $result->row();
		$order->contents	= $this->get_items($order->id);
		return $order;
	}
	function get_items($id){
		$this->db->select('order_id, contents');
		$this->db->where('order_id', $id);
		$result	= $this->db->get('order_items');
		
		$items	= $result->result_array();
		$return	= array();
		$count	= 0;
		foreach($items as $item)
		{
			$item_content	= unserialize($item['contents']);
			//remove contents from the item array
			unset($item['contents']);
			$return[$count]	= $item;
			//merge the unserialized contents with the item array
			$return[$count]	= array_merge($return[$count], $item_content);
			
			$count++;
		}
		return $return;
	}
	
	function delete($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('orders');
		
		//now delete the order items
		$this->db->where('order_id', $id);
		$this->db->delete('order_items');
	}
	function update_score_customer($data){
		$order = $this->db->select("customers.id,customers.group_id as level,customers.score,orders.total,orders.score_minus as order_score,customers.score,orders.status,orders.id as order_id")->where("orders.id",$data["id"])->join("customers","customers.id = orders.customer_id","right")->get("orders")->row();	
		
		//$order_total = (int)!empty($history)?$order->total - $history->shipping_location->total_price_shipping:$order->total;
		if($data["status"] == "Delivered" and $order->status != "Delivered"){
			$info_items = $this->get_items($order->order_id);
			$score = 0;
			foreach($info_items as $item){
				$score += $item["total_score_product"];
			}
			$data_update = array("score"=>$order->score+$score);
			$this->db->where("id",$order->id)->update("customers",$data_update);
			//$this->go_cart->set_score_customer_shipping($order->id,$order_total,"verb");
			$this->update_level_customer($order->id,$order->score+$score,$order->level);
		}
		elseif(!empty($order) && $order->status == "Delivered" && $data["status"] != "Delivered"){
			$info_items = $this->get_items($order->order_id);
			$score = 0;
			foreach($info_items as $item){
				$score += $item["total_score_product"];
			}
			$data_update = array("score"=>$order->score-$score);
			$this->db->where("id",$order->id)->update("customers",$data_update);
			//$this->go_cart->set_score_customer_shipping($order->id,$order_total,"minus");
		}
		//echo 123;exit;
	}
	function update_level_customer($id_customer,$score_now,$level){
		if(($level == 1 or $level == 0)  and $score_now >= 5000){
			$this->db->where("id",$id_customer)->update("customers",array("group_id"=>3));
		}
	}
	function save_order($data, $contents = false){
		
		$this->update_score_customer($data);// cap nhat so diem trong khách hàng
		
		if (isset($data['id'])){
			$this->db->where('id', $data['id']);
			$this->db->update('orders', $data);
			$id = $data['id'];
			// we don't need the actual order number for an update
			$order_number = $id;
		}
		else{
			$this->db->insert('orders', $data);
			$id = $this->db->insert_id();
			//create a unique order number
			//unix time stamp + unique id of the order just submitted.
			$order	= array('order_number'=> date('U').$id);
			//update the order with this order id
			$this->db->where('id', $id);
			$this->db->update('orders', $order);
			//return the order id we generated
			$order_number = $order['order_number'];
		}
		
		//if there are items being submitted with this order add them now
		if($contents){
			// clear existing order items
			$this->db->where('order_id', $id)->delete('order_items');
			// update order items
			foreach($contents as $item){
				$save				= array();
				$save['contents']	= $item;
				$item				= unserialize($item);
				$save['product_id'] = $item['id'];
				$save['quantity'] 	= $item['quantity'];
				$save['order_id']	= $id;
				$this->db->insert('order_items',$save);
			}
		}
		
		return array($order_number,$id);

	}
	
	function get_best_sellers($start, $end){
		if(!empty($start))
		{
			$this->db->where('ordered_on >=', $start);
		}
		if(!empty($end))
		{
			$this->db->where('ordered_on <',  $end);
		}
		
		// just fetch a list of order id's
		$orders	= $this->db->select('id')->get('orders')->result();
		
		$items = array();
		$subtotal = array();
		foreach($orders as $order)
		{
			// get a list of product id's and quantities for each
			$order_items	= $this->db->select('product_id, quantity,contents')->where('order_id', $order->id)->get('order_items')->result_array();
			foreach($order_items as $i)
			{
				$contents = unserialize($i["contents"]);
				$subtotal[$i['product_id']] += $contents["subtotal"];
				if(isset($items[$i['product_id']]))
				{
					$items[$i['product_id']]	+= $i['quantity'];
					
				}
				else
				{
					$items[$i['product_id']]	= $i['quantity'];
				}
				
			}
		}
		arsort($items);

		// don't need this anymore
		unset($orders);
		
		$return	= array();
		foreach($items as $key=>$quantity)
		{
			$product				= $this->db->where('id', $key)->get('products')->row();
			if($product)
			{
				$product->quantity_sold	= $quantity;
				$product->subtotal	= $subtotal[$key];
			}
			else
			{
				
				$product = (object) array('sku'=>'Deleted', 'name'=>'Deleted', 'quantity_sold'=>$quantity,"subtotal"=>$subtotal[$key]);
			}
			$return[] = $product;
		}
				
		return $return;
	}
	function get_category_name($list_id){
		$str_list_id = implode(",",$list_id);
		$this->db->where("category_products.product_id IN (".$str_list_id.")");
		$this->db->join("category_products","category_products.category_id = categories.id");
		$this->db->select("categories.slug,category_products.product_id");
		$data = $this->db->get("categories")->result();
		if(!empty($data)) foreach($data as $item){
			$list_name_category[$item->product_id] = $item->slug;	
		}
		return $list_name_category;
	}
	public function get_score_order($id_customer){
		return $this->db->select("id,SUM()")->where("status <>","Delivered")->where("status <>","Cancelled")->get('orders')->result();
	}
	public function get_count_order($id_customer){
		return $this->db->select("COUNT(id) as totalorder")->where("customer_id",$id_customer)->where("status","Delivered")->get("orders")->row();	
	}
	public function get_history($id_order){
		return $this->db->where("order_number",$id_order)->get("order_history")->row();
	}
}